import 'package:drift/drift.dart';
import 'package:drift/wasm.dart';

part 'app_database_web.g.dart';

@DataClassName('UserData')
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get username => text()();
  TextColumn get email => text()();
  TextColumn get avatar => text().nullable()();
  TextColumn get role => text()();
  BoolColumn get isActive => boolean().withDefault(Constant(true))();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
}

@DataClassName('ProjectData')
class Projects extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get projectName => text()();
  TextColumn get description => text().nullable()();
  TextColumn get status => text()();
  IntColumn get ownerId => integer()();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
}

@DataClassName('TodoData')
class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  TextColumn get description => text().nullable()();
  TextColumn get status => text()();
  IntColumn get projectId => integer()();
  IntColumn get assigneeId => integer().nullable()();
  IntColumn get reporterId => integer()();
  IntColumn get estimatedHours => integer().nullable()();
  IntColumn get actualHours => integer().nullable()();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get dueDate => dateTime().nullable()();
}

@DriftDatabase(tables: [Users, Projects, Todos])
class AppDatabaseWeb extends _$AppDatabaseWeb {
  AppDatabaseWeb() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  // 用户相关操作
  Future<List<UserData>> getAllUsers() => select(users).get();

  Future<UserData?> findUserById(int id) =>
      (select(users)..where((u) => u.id.equals(id))).getSingleOrNull();

  Future<int> insertUser(Insertable<UserData> user) => into(users).insert(user);

  Future<bool> updateUser(Insertable<UserData> user) =>
      update(users).replace(user);

  Future<int> deleteUser(int id) =>
      (delete(users)..where((u) => u.id.equals(id))).go();

  // 项目相关操作
  Future<List<ProjectData>> getAllProjects() => select(projects).get();

  Future<ProjectData?> findProjectById(int id) =>
      (select(projects)..where((p) => p.id.equals(id))).getSingleOrNull();

  Future<int> insertProject(Insertable<ProjectData> project) =>
      into(projects).insert(project);

  Future<bool> updateProject(Insertable<ProjectData> project) =>
      update(projects).replace(project);

  Future<int> deleteProject(int id) =>
      (delete(projects)..where((p) => p.id.equals(id))).go();

  // 待办相关操作
  Future<List<TodoData>> getAllTodos() => select(todos).get();

  Future<TodoData?> findTodoById(int id) =>
      (select(todos)..where((t) => t.id.equals(id))).getSingleOrNull();

  Future<int> insertTodo(Insertable<TodoData> todo) => into(todos).insert(todo);

  Future<bool> updateTodo(Insertable<TodoData> todo) =>
      update(todos).replace(todo);

  Future<int> deleteTodo(int id) =>
      (delete(todos)..where((t) => t.id.equals(id))).go();

  // 根据项目ID获取待办事项
  Future<List<TodoData>> getTodosByProjectId(int projectId) {
    return (select(todos)..where((t) => t.projectId.equals(projectId))).get();
  }

  // 根据分配人ID获取待办事项
  Future<List<TodoData>> getTodosByAssigneeId(int assigneeId) {
    return (select(todos)..where((t) => t.assigneeId.equals(assigneeId))).get();
  }
}

DatabaseConnection _openConnection() {
  // 使用新的 WasmDatabase.open 替代已弃用的 WebDatabase
  return DatabaseConnection.delayed(Future(() async {
    final result = await WasmDatabase.open(
      databaseName: 'flowplan',
      sqlite3Uri: Uri.parse('sqlite3.wasm'),
      driftWorkerUri: Uri.parse('drift_worker.dart.js'),
    );

    return result.resolvedExecutor;
  }));
}
